SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE [dbo].[usp_StatisticsFieldUpdate]
	(
      @param_JOL_Statistics_StartTime VARCHAR(50),
      @param_JOL_Statistics_EndTime VARCHAR(50)
	)
AS
SET @param_JOL_Statistics_StartTime = @param_JOL_Statistics_StartTime + '-01'
SET @param_JOL_Statistics_EndTime = @param_JOL_Statistics_EndTime + '-01'
DECLARE @Count int, @Lecture int, @Interview int, @WrittenTest int, @TheOthers int

WHILE @param_JOL_Statistics_StartTime <= @param_JOL_Statistics_EndTime
BEGIN
	SET @Lecture =(SELECT count(*)  
		FROM [dbo].[JOL_ApplyField]
		WHERE YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
			AND JOL_Field_Purpose = '宣讲会'
			AND JOL_Field_VerifyStatus = '已审核' )
	SET @Interview = (SELECT count(*) 
		FROM [dbo].[JOL_ApplyField]
		WHERE YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
			AND JOL_Field_Purpose = '面试'
			AND JOL_Field_VerifyStatus = '已审核')
	SET @WrittenTest = (SELECT count(*) 
		FROM [dbo].[JOL_ApplyField]
		WHERE YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
			AND JOL_Field_Purpose = '笔试'
			AND JOL_Field_VerifyStatus = '已审核')
	SET @TheOthers = (SELECT count(*) 
		FROM [dbo].[JOL_ApplyField]
		WHERE YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
			AND JOL_Field_Purpose = '其他'
			AND JOL_Field_VerifyStatus = '已审核')

	SET @Count = (SELECT COUNT(*) FROM dbo.JOL_Statistics_Field WHERE @param_JOL_Statistics_StartTime = Statistics_Date)
	IF (@Count = 0)
		BEGIN
			INSERT INTO [dbo].[JOL_Statistics_Field]
			(
				Statistics_Date,
				Statistics_Lecture,
				Statistics_Interview,
				Statistics_WrittenTest,
				Statistics_TheOthers,
				Statistics_AddTime,
				Statistics_UpdateTime
			)
			VALUES
			(
				@param_JOL_Statistics_StartTime,
				@Lecture,
				@Interview,
				@WrittenTest,
				@TheOthers,
				GETDATE(),
				GETDATE()
			)
		END
	ELSE
		BEGIN
			UPDATE [dbo].[JOL_Statistics_Field]
				SET Statistics_Lecture = @Lecture,
					Statistics_Interview = @Interview,
					Statistics_WrittenTest = @WrittenTest,
					Statistics_TheOthers = @TheOthers,
					Statistics_UpdateTime = GETDATE()
			WHERE Statistics_Date = @param_JOL_Statistics_StartTime
			
		END
	SET @param_JOL_Statistics_StartTime = CONVERT(VARCHAR(50), DATEADD(MONTH, 1, @param_JOL_Statistics_StartTime), 23)
END
GO
